package com.gw2.gw2world.controller;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.StringReader;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.content.SharedPreferences.Editor;
import android.content.res.AssetManager;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.preference.PreferenceManager;
import android.util.JsonReader;

public class DatabaseOpenHelper extends SQLiteOpenHelper {
	@SuppressWarnings("unused")
	private static final String TAG="DatabaseOpenHelper";
	private AssetManager am;
	private SharedPreferences sp;
	public DatabaseOpenHelper(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);
		am=context.getAssets();
		sp=PreferenceManager.getDefaultSharedPreferences(context);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			//create structure and add event properties
			StringBuilder sb=new StringBuilder();
			BufferedReader br=new BufferedReader(new InputStreamReader(am.open("database.sql",AssetManager.ACCESS_BUFFER), "UTF-8"));
			try {
				String line=null;
				while((line=br.readLine())!=null) {
					sb.append(new String(line.substring(0, line.indexOf("--")==-1?line.length():line.indexOf("--")-1))); //remove comment and append
					if(line.contains(");")) {
						//android.util.Log.d(TAG, sb.toString());
						db.execSQL(sb.toString());
						sb=new StringBuilder();
					}
				}
				final Editor e=sp.edit();
				e.putBoolean("installed", true);
				e.commit();
			} finally {
				br.close();
			}
		} catch (IOException e) {e.printStackTrace();}
	}

	public void insertWorldEvents() {
		try {
			// create structure and add event properties
			StringBuilder sb = new StringBuilder();
			BufferedReader br = new BufferedReader(new InputStreamReader(am.open("world_event.sql",AssetManager.ACCESS_BUFFER), "UTF-8"));
			try {
				String line = null;
				while ((line = br.readLine()) != null) {
					sb.append(new String(line.substring(0,line.indexOf("--") == -1 ? line.length() : line.indexOf("--") - 1))); // remove comment and append
					if (line.contains(";")) {
						// android.util.Log.d(TAG, sb.toString());
						getWritableDatabase().execSQL(sb.toString());
						sb = new StringBuilder();
					}
				}
			} finally {
				br.close();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public void installLivingWorldEvents() {
		//android.util.Log.d(TAG, "Installing living world events");
		try {
			BufferedReader br = new BufferedReader(new InputStreamReader(am.open("living_world.txt",AssetManager.ACCESS_BUFFER), "UTF-8"));
			try {
				String line = null;
				while ((line = br.readLine()) != null) {
					getWritableDatabase().execSQL("UPDATE event SET flag=flag|32 WHERE id=?", new String[]{line});
				}
			} finally {
				br.close();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Start to read JSON, servers will be inserted by the readServer() function.
	 * @param json JSON String
	 * @param lang language code (ie: en, fr, ...)
	 */
	public void installServers(String json, String lang) {
		//getWritableDatabase().delete("server", "lang=?", new String[]{lang});
		try {
			StringReader sr=new StringReader(json);
			JsonReader jr=new JsonReader(sr);
			try {
				ContentValues cv;
				jr.beginArray();
				while(jr.hasNext()) {
					jr.beginObject();
					String tag, name="";
					int id=0;
					while(jr.hasNext()) {
						tag=jr.nextName();
						if(tag.equals("id")) id=jr.nextInt();
						else if(tag.equals("name")) name=jr.nextString();
						else jr.skipValue();
					}
					jr.endObject();
					cv=new ContentValues(3);
					cv.put("id",id);
					cv.put("lang",lang);
					cv.put("name",name);
					getWritableDatabase().insertWithOnConflict("server", null, cv, SQLiteDatabase.CONFLICT_IGNORE);
				}
			} finally {
				jr.close();
				sr.close();
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * Put map names into database from downloaded JSON. It also set map by region (hard coded).
	 * @param json JSON String
	 * @param lang language code (ie: en, fr, ...)
	 */
	public void installMaps(String json, String lang) {
		//getWritableDatabase().delete("location", "lang=?", new String[]{lang});
		try {
			StringReader sr=new StringReader(json);
			JsonReader jr=new JsonReader(sr);
			try {
				ContentValues cv;
				jr.beginArray();
				while(jr.hasNext()) {
					jr.beginObject();
					String tag, name="";
					int id=0;
					while(jr.hasNext()) {
						tag=jr.nextName();
						if(tag.equals("id")) id=jr.nextInt();
						else if(tag.equals("name")) name=jr.nextString();
						else jr.skipValue();
					}
					jr.endObject();
					cv=new ContentValues(3);
					cv.put("id",id);
					cv.put("lang",lang);
					cv.put("name",name);
					getWritableDatabase().insertWithOnConflict("location", null, cv, SQLiteDatabase.CONFLICT_IGNORE);
				}
			} finally {
				jr.close();
				sr.close();
			}
			/* Does not work:
			getWritableDatabase().rawQuery("UPDATE location SET region_id=1 WHERE id IN (50,22,24,23,15,17,873)", null);
			getWritableDatabase().rawQuery("UPDATE location SET region_id=2 WHERE id IN (24,21,32,25,20,19)", null);
			getWritableDatabase().rawQuery("UPDATE location SET region_id=3 WHERE id IN (29,31,26,27,28,30)", null);
			getWritableDatabase().rawQuery("UPDATE location SET region_id=4 WHERE id IN (39,53,54,73,34,35)", null);
			getWritableDatabase().rawQuery("UPDATE location SET region_id=5 WHERE id IN (62,65,51)", null);*/
		} catch(Exception e) {
			e.printStackTrace();
		}
		ContentValues cv;
		//add map region
		for(Integer n : new Integer[]{/*50,*/23,24,15,17,73,873}) { //kryta (50 is Lion's Arch)
			cv=new ContentValues();
			cv.put("region_id", 1);
			getWritableDatabase().update("location", cv, "id=?", new String[]{String.valueOf(n)});
		}
		for(Integer n : new Integer[]{21,22,32,25,20,19}) { //ascalon
			cv=new ContentValues();
			cv.put("region_id", 2);
			getWritableDatabase().update("location", cv, "id=?", new String[]{String.valueOf(n)});
		}
		for(Integer n : new Integer[]{29,31,26,27,28,30}) { //shiverpeak
			cv=new ContentValues();
			cv.put("region_id", 3);
			getWritableDatabase().update("location", cv, "id=?", new String[]{String.valueOf(n)});
		}
		for(Integer n : new Integer[]{39,53,54,34,35}) { //maguuma
			cv=new ContentValues();
			cv.put("region_id", 4);
			getWritableDatabase().update("location", cv, "id=?", new String[]{String.valueOf(n)});
		}
		for(Integer n : new Integer[]{62,65,51}) { //orr
			cv=new ContentValues();
			cv.put("region_id", 5);
			getWritableDatabase().update("location", cv, "id=?", new String[]{String.valueOf(n)});
		}
	}
	/**
	 * Put map information (min and max level for now) into database from downloaded JSON.
	 * @param json JSON String
	 */
	public void installMapDetails(String json) {
		try {
			StringReader sr=new StringReader(json);
			JsonReader jr=new JsonReader(sr);
			try {
				jr.beginObject();
				String tag;
				while(jr.hasNext()) {
					tag=jr.nextName();
					if(tag.equals("maps")) {
						jr.beginObject();
						ContentValues cv=new ContentValues(2);
						int id=0;
						while(jr.hasNext()) {
							tag=jr.nextName(); //map ID
							try {
								//android.util.Log.d(TAG, " - "+tag);
								cv=new ContentValues(2);
								id=Integer.parseInt(tag);
								jr.beginObject();
								while(jr.hasNext()) {
									tag=jr.nextName();
									if(tag.equals("min_level")) cv.put("min_level", jr.nextInt());
									else if(tag.equals("max_level")) cv.put("max_level",jr.nextInt());
									else jr.skipValue();
								}
								jr.endObject();
								//android.util.Log.d(TAG, String.valueOf(id)+": "+cv.getAsString("min_level")+"-"+cv.getAsString("max_level"));
								getWritableDatabase().update("location", cv, "id=?", new String[]{String.valueOf(id)});
							} catch(Exception e) {
								//e.printStackTrace();
								jr.skipValue();
							}
						}
						jr.endObject();
					}
					else jr.skipValue();
				}
			} finally {
			jr.close();
			sr.close();
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * Put event names into database from downloaded JSON. It also add skill points.
	 * @param json JSON String
	 */
	public void installEvents(String json, String lang) {
		//getWritableDatabase().delete("event_name", "lang=?", new String[]{lang});
		ContentValues cv;
		try {
			StringReader sr=new StringReader(json);
			JsonReader jr=new JsonReader(sr);
			try {
				jr.beginArray();
				while(jr.hasNext()) {
					jr.beginObject();
					String tag;
					cv=new ContentValues(3);
					while(jr.hasNext()) {
						tag=jr.nextName();
						if(tag.equals("id")) cv.put("id", jr.nextString());
						else if(tag.equals("name")) cv.put("name",jr.nextString());
						else jr.skipValue();
					}
					jr.endObject();
					cv.put("lang",lang);
					getWritableDatabase().insertWithOnConflict("event_name", null, cv, SQLiteDatabase.CONFLICT_IGNORE);
				}
			} finally {
				jr.close();
				sr.close();
			}
			//event type
			BufferedReader br = new BufferedReader(new InputStreamReader(am.open("skill_events.json",AssetManager.ACCESS_BUFFER)));
			jr=new JsonReader(br);
			jr.beginArray();
			while(jr.hasNext()) {
				cv=new ContentValues(1);
				cv.put("flag", 4);
				getWritableDatabase().update("event", cv, "id=?", new String[]{jr.nextString()});
			}
			jr.close();
			br.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * Put event detail into database from downloaded JSON.
	 * @param json JSON String
	 */
	public void installEventDetails(String json) {
		//getWritableDatabase().delete("event", null, null);
		try {
			StringReader sr=new StringReader(json);
			JsonReader jr=new JsonReader(sr);
			try {
				ContentValues cv;
				jr.beginObject();
				String tag;
				int flag;
				while(jr.hasNext()) {
					tag=jr.nextName();
					if(tag.equals("events")) {
						jr.beginObject();
						cv=new ContentValues();
						while(jr.hasNext()) {
							tag=jr.nextName();
							cv.put("id", tag);
							jr.beginObject();
							while(jr.hasNext()) {
								tag=jr.nextName();
								if(tag.equals("level")) cv.put("level", jr.nextInt());
								else if(tag.equals("map_id")) cv.put("location", jr.nextInt());
								else if(tag.equals("flags")) {
									jr.beginArray();
									flag=0;
									while(jr.hasNext()) {
										tag=jr.nextString();
										if(tag.equals("group_event")) flag+=1;
										else if(tag.equals("map_wide")) flag+=2;
										else jr.skipValue();
									}
									jr.endArray();
									cv.put("flag", flag);
								} else jr.skipValue();
							}
							jr.endObject();
							getWritableDatabase().insertWithOnConflict("event", null, cv, SQLiteDatabase.CONFLICT_IGNORE);
						}
						jr.endObject();
					}
					else jr.skipValue();
				}
			} finally {
				jr.close();
				sr.close();
			}
			StringBuilder sb=new StringBuilder();
			BufferedReader br=new BufferedReader(new InputStreamReader(am.open("world_event.sql",AssetManager.ACCESS_BUFFER), "UTF-8"));
			try {
				String line=null;
				while((line=br.readLine())!=null) {
					sb.append(new String(line.substring(0, line.indexOf("--")==-1?line.length():line.indexOf("--")-1))); //remove comment and append
					if(line.contains(");")) {
						//android.util.Log.d(TAG, sb.toString());
						getWritableDatabase().execSQL(sb.toString());
						sb=new StringBuilder();
					}
				}
			} finally {
				br.close();
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVer, int newVer) {}
}
